﻿------------------------------------------- HE THONG------------------------------------------
--DROP TABLE QLL_TRANG_THAI_THANG_LUONG;
CREATE TABLE QLL_TRANG_THAI_THANG_LUONG 
(
       THANG INT,
       NAM INT,
       TRANG_THAI INT,-- 0: dang cho tinh luong, 1: da tinh luong, 2: da khoa
       PRIMARY KEY(THANG, NAM)
);

INSERT INTO QLL_TRANG_THAI_THANG_LUONG VALUES(8,2013,2);
INSERT INTO QLL_TRANG_THAI_THANG_LUONG VALUES(9,2013,2);
INSERT INTO QLL_TRANG_THAI_THANG_LUONG VALUES(10,2013,0);
------------------------------------------- HE THONG------------------------------------------
--DROP TABLE HT_DM_LOAI_CAN_BO;
CREATE TABLE HT_DM_LOAI_CAN_BO 
(
       MA_LOAI_CAN_BO NVARCHAR2(500) PRIMARY KEY,
       TEN_LOAI_CAN_BO NVARCHAR2(500) NOT NULL UNIQUE,
       STT_LOAI_CAN_BO INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO HT_DM_LOAI_CAN_BO(MA_LOAI_CAN_BO,TEN_LOAI_CAN_BO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_CAN_BO) VALUES('GIANG_VIEN_BIEN_CHE','Giảng viên biên chế',0, SYSDATE,100);
INSERT INTO HT_DM_LOAI_CAN_BO(MA_LOAI_CAN_BO,TEN_LOAI_CAN_BO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_CAN_BO) VALUES('GIANG_VIEN_HOP_DONG','Giảng viên hợp đồng',0, SYSDATE,100);
INSERT INTO HT_DM_LOAI_CAN_BO(MA_LOAI_CAN_BO,TEN_LOAI_CAN_BO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_CAN_BO) VALUES('GIANG_VIEN_THINH_GIANG','Giảng viên thỉnh giảng',0, SYSDATE,100);
INSERT INTO HT_DM_LOAI_CAN_BO(MA_LOAI_CAN_BO,TEN_LOAI_CAN_BO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_CAN_BO) VALUES('CAN_BO_KHONG_GIANG_DAY','Cán bộ không giảng dạy',0, SYSDATE,100);
INSERT INTO HT_DM_LOAI_CAN_BO(MA_LOAI_CAN_BO,TEN_LOAI_CAN_BO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_CAN_BO) VALUES('TRO_GIANG','Trợ giảng',0, SYSDATE,100);


--DROP TABLE HT_DM_DAN_TOC;
CREATE TABLE HT_DM_DAN_TOC 
(
       MA_DAN_TOC NVARCHAR2(500) PRIMARY KEY,
       TEN_DAN_TOC NVARCHAR2(500) NOT NULL UNIQUE,
       STT_DAN_TOC INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO HT_DM_DAN_TOC(MA_DAN_TOC,TEN_DAN_TOC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DAN_TOC) VALUES('KINH','Kinh',0, SYSDATE,100);
INSERT INTO HT_DM_DAN_TOC(MA_DAN_TOC,TEN_DAN_TOC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DAN_TOC) VALUES('KHOME','Khơme',0, SYSDATE,100);
INSERT INTO HT_DM_DAN_TOC(MA_DAN_TOC,TEN_DAN_TOC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DAN_TOC) VALUES('TAY','Tày',0, SYSDATE,100);
INSERT INTO HT_DM_DAN_TOC(MA_DAN_TOC,TEN_DAN_TOC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DAN_TOC) VALUES('NUNG','Nùng',0, SYSDATE,100);
INSERT INTO HT_DM_DAN_TOC(MA_DAN_TOC,TEN_DAN_TOC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DAN_TOC) VALUES('HOA','Hoa',0, SYSDATE,100);

--DROP TABLE HT_DM_TON_GIAO;
CREATE TABLE HT_DM_TON_GIAO 
(
       MA_TON_GIAO NVARCHAR2(500) PRIMARY KEY,
       TEN_TON_GIAO NVARCHAR2(500) NOT NULL UNIQUE,
       STT_TON_GIAO INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO HT_DM_TON_GIAO(MA_TON_GIAO,TEN_TON_GIAO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TON_GIAO) VALUES('KHONG','Không',0, SYSDATE,100);
INSERT INTO HT_DM_TON_GIAO(MA_TON_GIAO,TEN_TON_GIAO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TON_GIAO) VALUES('PHAT_GIAO','Phật giáo',0, SYSDATE,100);
INSERT INTO HT_DM_TON_GIAO(MA_TON_GIAO,TEN_TON_GIAO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TON_GIAO) VALUES('THIEN_CHUA','Thiên chúa',0, SYSDATE,100);
INSERT INTO HT_DM_TON_GIAO(MA_TON_GIAO,TEN_TON_GIAO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TON_GIAO) VALUES('HOA_HAO','Hòa hảo',0, SYSDATE,100);
INSERT INTO HT_DM_TON_GIAO(MA_TON_GIAO,TEN_TON_GIAO,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TON_GIAO) VALUES('CAO_DAI','Cao đài',0, SYSDATE,100);
--DROP TABLE HT_DM_QUOC_TICH;
CREATE TABLE HT_DM_QUOC_TICH 
(
       MA_QUOC_TICH NVARCHAR2(500) PRIMARY KEY,
       TEN_QUOC_TICH NVARCHAR2(500) NOT NULL UNIQUE,
       STT_QUOC_TICH INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO HT_DM_QUOC_TICH(MA_QUOC_TICH,TEN_QUOC_TICH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_QUOC_TICH) VALUES('VIET_NAM','Việt nam',0, SYSDATE,100);
INSERT INTO HT_DM_QUOC_TICH(MA_QUOC_TICH,TEN_QUOC_TICH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_QUOC_TICH) VALUES('UC','Úc',0, SYSDATE,100);
INSERT INTO HT_DM_QUOC_TICH(MA_QUOC_TICH,TEN_QUOC_TICH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_QUOC_TICH) VALUES('Mỹ','Mỹ',0, SYSDATE,100);

--DROP TABLE HT_DM_LOAI_GIANG_VIEN;
CREATE TABLE HT_DM_LOAI_GIANG_VIEN 
(
       MA_LOAI_GIANG_VIEN NVARCHAR2(500) PRIMARY KEY,
       TEN_LOAI_GIANG_VIEN NVARCHAR2(500) NOT NULL UNIQUE,
       STT_LOAI_GIANG_VIEN INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO HT_DM_LOAI_GIANG_VIEN(MA_LOAI_GIANG_VIEN,TEN_LOAI_GIANG_VIEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_GIANG_VIEN) VALUES('CO_HUU','Cơ hữu',0, SYSDATE,100);
INSERT INTO HT_DM_LOAI_GIANG_VIEN(MA_LOAI_GIANG_VIEN,TEN_LOAI_GIANG_VIEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_GIANG_VIEN) VALUES('HOP_DONG','Hợp đồng',0, SYSDATE,100);
--DROP TABLE QLCB_DM_DON_VI_TINH;
CREATE TABLE HT_DM_DON_VI_TINH 
(
       MA_DON_VI_TINH NVARCHAR2(500) PRIMARY KEY,
       TEN_DON_VI_TINH NVARCHAR2(500) NOT NULL UNIQUE,
       STT_DON_VI_TINH INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO HT_DM_DON_VI_TINH(MA_DON_VI_TINH,TEN_DON_VI_TINH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DON_VI_TINH) VALUES('TIET','Tiết',0, SYSDATE,100);
INSERT INTO HT_DM_DON_VI_TINH(MA_DON_VI_TINH,TEN_DON_VI_TINH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DON_VI_TINH) VALUES('LUAN_VAN','Luận văn',0, SYSDATE,100);


--DROP TABLE HT_DM_HOC_HAM;
CREATE TABLE HT_DM_HOC_HAM 
(
       MA_HOC_HAM NVARCHAR2(500) PRIMARY KEY,
       TEN_HOC_HAM NVARCHAR2(500) NOT NULL UNIQUE,
       STT_HOC_HAM INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO HT_DM_HOC_HAM(MA_HOC_HAM,TEN_HOC_HAM,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_HAM) VALUES('GIANG_VIEN','Giảng viên',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_HAM(MA_HOC_HAM,TEN_HOC_HAM,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_HAM) VALUES('GIANG_VIEN_CHINH','Giảng viên chính',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_HAM(MA_HOC_HAM,TEN_HOC_HAM,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_HAM) VALUES('GIANG_VIEN_CAO_CAP','Giảng viên cao cấp',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_HAM(MA_HOC_HAM,TEN_HOC_HAM,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_HAM) VALUES('GIAO_SU','Giáo sư',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_HAM(MA_HOC_HAM,TEN_HOC_HAM,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_HAM) VALUES('PHO_GIAO_SU','Phó giáo sư',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_HAM(MA_HOC_HAM,TEN_HOC_HAM,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_HAM) VALUES('CHUYEN_VIEN','Chuyên viên',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_HAM(MA_HOC_HAM,TEN_HOC_HAM,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_HAM) VALUES('CHUYEN_VIEN_CHINH','Chuyên viên chính',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_HAM(MA_HOC_HAM,TEN_HOC_HAM,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_HAM) VALUES('TRO_GIANG','Trợ giảng',0, SYSDATE,100);


--DROP TABLE HT_DM_HOC_VI;
CREATE TABLE HT_DM_HOC_VI 
(
       MA_HOC_VI NVARCHAR2(500) PRIMARY KEY,
       TEN_HOC_VI NVARCHAR2(500) NOT NULL UNIQUE,
       STT_HOC_VI INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO HT_DM_HOC_VI(MA_HOC_VI,TEN_HOC_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_VI) VALUES('THAC_SI','Thạc sĩ',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_VI(MA_HOC_VI,TEN_HOC_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_VI) VALUES('KY_SU','Kỹ sư',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_VI(MA_HOC_VI,TEN_HOC_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_VI) VALUES('CU_NHAN','Cử nhân',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_VI(MA_HOC_VI,TEN_HOC_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_VI) VALUES('TIEN_SI','Tiến sĩ',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_VI(MA_HOC_VI,TEN_HOC_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_VI) VALUES('CAO_DANG','Cao đẳng',0, SYSDATE,100);
INSERT INTO HT_DM_HOC_VI(MA_HOC_VI,TEN_HOC_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_HOC_VI) VALUES('TRUNG_CAP','Trung cấp',0, SYSDATE,100);


---------------------------------------------QUẢN LÝ CÁN BỘ----------------------------------------
--DROP TABLE TT_DM_LOAI_DON_VI;
--DELETE TT_DM_LOAI_DON_VI;
CREATE TABLE QLCB_DM_LOAI_DON_VI 
(
       MA_LOAI_DON_VI NVARCHAR2(100) PRIMARY KEY,
       TEN_LOAI_DON_VI NVARCHAR2(100) NOT NULL UNIQUE,
       STT_LOAI_DON_VI INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO QLCB_DM_LOAI_DON_VI(MA_LOAI_DON_VI,TEN_LOAI_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_DON_VI) VALUES('LANH_DAO_TRUONG','Lãnh đạo trường',0, SYSDATE,0);
INSERT INTO QLCB_DM_LOAI_DON_VI(MA_LOAI_DON_VI,TEN_LOAI_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_DON_VI) VALUES('KHOA','Khoa',0, SYSDATE,1);
INSERT INTO QLCB_DM_LOAI_DON_VI(MA_LOAI_DON_VI,TEN_LOAI_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_DON_VI) VALUES('PHONG','Phòng ',0, SYSDATE,2);
INSERT INTO QLCB_DM_LOAI_DON_VI(MA_LOAI_DON_VI,TEN_LOAI_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_LOAI_DON_VI) VALUES('TRUNG_TAM','Trung tâm',0, SYSDATE,3);


--DROP TABLE QLCB_DM_DON_VI;
--DELETE QLCB_DM_DON_VI;
CREATE TABLE QLCB_DM_DON_VI 
(
       MA_DON_VI NVARCHAR2(100) PRIMARY KEY,
       TEN_DON_VI NVARCHAR2(100) NOT NULL UNIQUE,
       MA_LOAI_DON_VI NVARCHAR2(100) NOT NULL REFERENCES QLCB_DM_LOAI_DON_VI(MA_LOAI_DON_VI),
       STT_DON_VI INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
ALTER TABLE QLCB_DM_DON_VI ADD MA_DON_VI_CHU_QUAN NVARCHAR2(100) REFERENCES QLCB_DM_DON_VI(MA_DON_VI);

INSERT INTO QLCB_DM_DON_VI(MA_DON_VI,MA_LOAI_DON_VI,TEN_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DON_VI) VALUES('LANH_DAO_TRUONG','LANH_DAO_TRUONG','Lãnh đạo trường',0, SYSDATE,100);
INSERT INTO QLCB_DM_DON_VI(MA_DON_VI,MA_LOAI_DON_VI,TEN_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DON_VI) VALUES('KHOA_CNTT_VA_TT','KHOA','Khoa CNTT và TT',0, SYSDATE,100);
INSERT INTO QLCB_DM_DON_VI(MA_DON_VI,MA_LOAI_DON_VI,TEN_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DON_VI) VALUES('TRUNG_TAM_TIN_HOC_NGOAI_NGU','TRUNG_TAM','Trung tâm tin học ngoại ngữ',0, SYSDATE,100);
INSERT INTO QLCB_DM_DON_VI(MA_DON_VI,MA_LOAI_DON_VI,TEN_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DON_VI) VALUES('PHONG_TO_CHUC_CAN_BO','PHONG','Phòng tổ chức cán bộ',0, SYSDATE,100);
INSERT INTO QLCB_DM_DON_VI(MA_DON_VI,MA_LOAI_DON_VI,TEN_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DON_VI) VALUES('PHONG_DAO_TAO','PHONG','Phòng đào tạo',0, SYSDATE,100);
INSERT INTO QLCB_DM_DON_VI(MA_DON_VI,MA_LOAI_DON_VI,TEN_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_DON_VI) VALUES('PHONG_TAI_CHINH_KE_TOAN','PHONG','Phòng tài chính kế toán',0, SYSDATE,100);

/*
DROP TABLE QLCB_DM_TO_CHUYEN_MON;
DELETE QLCB_DM_TO_CHUYEN_MON;
*/
CREATE TABLE QLCB_DM_TO_CHUYEN_MON 
(
       MA_TO_CHUYEN_MON INT PRIMARY KEY,
       TEN_TO_CHUYEN_MON NVARCHAR2(100) NOT NULL,
       MA_DON_VI NVARCHAR2(100) REFERENCES QLCB_DM_DON_VI(MA_DON_VI),
       STT_TO_CHUYEN_MON INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
/*
CREATE SEQUENCE SEQ_TO_CHUYEN_MON START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER TRI_TO_CHUYEN_MON BEFORE INSERT ON QLCB_DM_TO_CHUYEN_MON REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT SEQ_TO_CHUYEN_MON.NEXTVAL    INTO :NEW.MA_TO_CHUYEN_MON    FROM DUAL; END;

--1
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Ban giám hiệu','LANH_DAO_TRUONG',0, SYSDATE,0);
-- 2 3 4 5 6 7
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Lãnh đạo khoa','KHOA_CNTT_VA_TT',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Bộ môn CNPM','KHOA_CNTT_VA_TT',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Bộ môn KHMT','KHOA_CNTT_VA_TT',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Bộ môn MMT&TT','KHOA_CNTT_VA_TT',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Bộ môn HTTT','KHOA_CNTT_VA_TT',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Giáo vụ khoa','KHOA_CNTT_VA_TT',0, SYSDATE,0);
-- 8 9 10 11
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Lãnh đạo trung tâm','TRUNG_TAM_TIN_HOC_NGOAI_NGU',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Tổ văn phòng','TRUNG_TAM_TIN_HOC_NGOAI_NGU',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Tổ tin học','TRUNG_TAM_TIN_HOC_NGOAI_NGU',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Tổ ngoại ngữ','TRUNG_TAM_TIN_HOC_NGOAI_NGU',0, SYSDATE,0);
-- 12 13
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Lãnh đạo phòng','PHONG_TO_CHUC_CAN_BO',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Chuyên viên phòng','PHONG_TO_CHUC_CAN_BO',0, SYSDATE,0);
-- 14 15
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Lãnh đạo phòng','PHONG_DAO_TAO',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Chuyên viên phòng','PHONG_DAO_TAO',0, SYSDATE,0);
--16 17
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Lãnh đạo phòng','PHONG_TAI_CHINH_KE_TOAN',0, SYSDATE,0);
INSERT INTO QLCB_DM_TO_CHUYEN_MON(TEN_TO_CHUYEN_MON,MA_DON_VI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_TO_CHUYEN_MON) VALUES('Chuyên viên phòng','PHONG_TAI_CHINH_KE_TOAN',0, SYSDATE,0);
*/
--DROP TABLE PQ_DM_MENU;
CREATE TABLE PQ_DM_MENU 
(
       MA_MENU NVARCHAR2(100) PRIMARY KEY,
       TEN_MENU NVARCHAR2(100) NOT NULL UNIQUE,
       STT_MENU INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO PQ_DM_MENU(MA_MENU,TEN_MENU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_MENU) VALUES('QUAN_TRI_DANH_MUC','Quản trị danh mục',0, SYSDATE,0);
INSERT INTO PQ_DM_MENU(MA_MENU,TEN_MENU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_MENU) VALUES('TRANG_CHU','Trang chủ',0, SYSDATE,1);
INSERT INTO PQ_DM_MENU(MA_MENU,TEN_MENU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_MENU) VALUES('HE_THONG','Hệ thống',0, SYSDATE,2);
INSERT INTO PQ_DM_MENU(MA_MENU,TEN_MENU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_MENU) VALUES('QUAN_LY_CAN_BO','Quản lý cán bộ',0, SYSDATE,3);
INSERT INTO PQ_DM_MENU(MA_MENU,TEN_MENU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_MENU) VALUES('QUAN_LY_LUONG','Quản lý lương',0, SYSDATE,4);
INSERT INTO PQ_DM_MENU(MA_MENU,TEN_MENU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_MENU) VALUES('PHAN_QUYEN','Phân quyền',0, SYSDATE,5);
INSERT INTO PQ_DM_MENU(MA_MENU,TEN_MENU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_MENU) VALUES('TIM_KIEM_THONG_KE','Tìm kiếm thống kê',-1, SYSDATE,7);
INSERT INTO PQ_DM_MENU(MA_MENU,TEN_MENU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_MENU) VALUES('CONG_TAC_CAN_BO','Công tác cán bộ',0, SYSDATE,7);

/*
DROP TABLE PQ_DM_QUYEN;
DELETE FROM PQ_DM_QUYEN;
*/
CREATE TABLE PQ_DM_QUYEN 
(
       MA_QUYEN NVARCHAR2(100) PRIMARY KEY,
       TEN_QUYEN NVARCHAR2(100) NOT NULL UNIQUE,
       MA_MENU NVARCHAR2(100) NOT NULL REFERENCES PQ_DM_MENU(MA_MENU),
       APPS NVARCHAR2(100) NOT NULL,
       MODULES NVARCHAR2(100) NOT NULL,
       HIEN_THI_TREN_MENU INT DEFAULT 0 NOT NULL,
       HIEN_THI_NHAC_VIEC INT DEFAULT 0 NOT NULL,
       STT_QUYEN INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DOI_MAT_KHAU'             ,'Đổi mật khẩu'             ,'HE_THONG' ,'hethong'  ,'doimatkhau'    ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('THONG_TIN_CA_NHAN'        ,'Thông tin cá nhân'        ,'HE_THONG' ,'hethong'  ,'thongtincanhan',0, SYSDATE,0,100);

INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_LOAI_CAN_BO' ,'Danh mục loại cán bộ' ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'loaicanbo' ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_DAN_TOC'     ,'Danh mục dân tộc'     ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'dantoc'    ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_TON_GIAO'    ,'Danh mục tôn giáo'    ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'tongiao'   ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_QUOC_TICH'   ,'Danh mục quốc tịch'   ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'quoctich'  ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_DON_VI_TINH' ,'Danh mục đơn vị tính' ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'donvitinh' ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_HOC_HAM'     ,'Danh mục học hàm'     ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'hocham'    ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_HOC_VI'      ,'Danh mục học vị'      ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'hocvi'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_NAM_HOC'     ,'Danh mục năm học'     ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'namhoc'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_HOC_KY'      ,'Danh mục học kỳ'      ,'QUAN_TRI_DANH_MUC'  ,'hethong'  ,'hocky'     ,0, SYSDATE,1,100);

INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_QUYEN'       ,'Danh mục quyền'       ,'PHAN_QUYEN'  ,'phanquyen','quyen'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_NHOM_QUYEN'  ,'Danh mục nhóm quyền'  ,'PHAN_QUYEN'  ,'phanquyen','nhomquyen' ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_MENU'        ,'Danh mục menu'        ,'PHAN_QUYEN'  ,'phanquyen','menu'      ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_CHUC_VU'     ,'Danh mục chức vụ'     ,'PHAN_QUYEN'  ,'phanquyen','chucvu'     ,0, SYSDATE,1,100);

INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_LOAI_DON_VI'  ,'Danh mục loại đơn vị'  ,'QUAN_LY_CAN_BO'  ,'qlcb','loaidonvi'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_DON_VI'       ,'Danh mục đơn vị'       ,'QUAN_LY_CAN_BO'  ,'qlcb','donvi'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_TO_CHUYEN_MON','Danh mục tổ chuyên môn','QUAN_LY_CAN_BO'  ,'qlcb','tochuyenmon'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_CAN_BO'       ,'Danh mục cán bộ'       ,'QUAN_LY_CAN_BO'  ,'qlcb','canbo'     ,0, SYSDATE,1,100);

INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DM_LOAI_THAI_SAN'      ,'Danh mục hình thức thai sản' ,'CONG_TAC_CAN_BO'  ,'qlcb','loaithaisan'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DM_LOAI_LOP_CO_VAN'    ,'Danh mục loại lớp cố vấn'    ,'CONG_TAC_CAN_BO'  ,'qlcb','loailopcovan'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DM_LOAI_LOP_DAY'       ,'Danh mục loại lớp dạy'       ,'CONG_TAC_CAN_BO'  ,'qlcb','loailopday'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DM_LOAI_DAO_TAO'       ,'Danh mục hình thức đào tạo'  ,'CONG_TAC_CAN_BO'  ,'qlcb','loaidaotao'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DM_LOAI_BCKH'          ,'Danh mục loại BCKH'          ,'CONG_TAC_CAN_BO'  ,'qlcb','loaibckh'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DM_LOAI_GIAO_TRINH'    ,'Danh mục loại giáo trình'    ,'CONG_TAC_CAN_BO'  ,'qlcb','loaigiaotrinh' ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DM_LOAI_NCKH'          ,'Danh mục loại NCKH'          ,'CONG_TAC_CAN_BO'  ,'qlcb','loainckh' ,0, SYSDATE,1,100);


INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('BAC_NGACH'             ,'Bậc/ngạch'              ,'QUAN_LY_LUONG'  ,'quanlyluong','bacngach'     ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('DANH_MUC_NGACH'        ,'Danh mục ngạch'         ,'QUAN_LY_LUONG'  ,'quanlyluong','ngach'   ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('PHU_CAP_CHUC_VU'       ,'Phụ cấp chức vụ'        ,'QUAN_LY_LUONG'  ,'quanlyluong','phucapchucvu'   ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('PHU_CAP_KHAC'          ,'Phụ cấp khác'           ,'QUAN_LY_LUONG'  ,'quanlyluong','phucapkhac'   ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('LUONG_CAN_BO'          ,'Quá trình lương cán bộ' ,'QUAN_LY_LUONG'  ,'quanlyluong','luongcanbo'   ,0, SYSDATE,1,1);


INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('TRA_CUU_CAN_BO'        ,'Tra cứu cán bộ'        ,'TIM_KIEM_THONG_KE'        ,'timkiemthongke'     ,'canbo'   ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('THONG_KE_LUONG_CAN_BO' ,'Thống kê lương cán bộ' ,'TIM_KIEM_THONG_KE'        ,'timkiemthongke'     ,'luongcanbo'   ,1, SYSDATE,1,100);

INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('CAN_BO_BCKH'           ,'Báo cáo khoa học'      ,'CONG_TAC_CAN_BO'        ,'giangvien'     ,'bckh'          ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('CAN_BO_NCKH'           ,'Nghiên cứu khoa học'   ,'CONG_TAC_CAN_BO'        ,'giangvien'     ,'nckh'          ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('CAN_BO_VIET_GIAO_TRINH','Viết giáo trình'       ,'CONG_TAC_CAN_BO'        ,'giangvien'     ,'vietgiaotrinh' ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('CAN_BO_GIANG_DAY'      ,'Giảng dạy'             ,'CONG_TAC_CAN_BO'        ,'giangvien'     ,'lopgiangday'      ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('CAN_BO_NCKH'           ,'Nghiên cứu khoa học'   ,'CONG_TAC_CAN_BO'        ,'giangvien'     ,'nckh'          ,0, SYSDATE,1,100);
INSERT INTO PQ_DM_QUYEN(MA_QUYEN,TEN_QUYEN,MA_MENU,APPS,MODULES,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,HIEN_THI_TREN_MENU,STT_QUYEN) VALUES('CAN_BO_CO_VAN_HOC_TAP' ,'Cố vấn học tập'        ,'CONG_TAC_CAN_BO'        ,'giangvien'     ,'lopcovan'   ,0, SYSDATE,1,100);

/*
DROP TABLE QLCB_DM_CAN_BO;
DELETE QLCB_DM_CAN_BO;
*/
CREATE TABLE QLCB_DM_CAN_BO 
(
       MA_CAN_BO INT PRIMARY KEY,
       TEN_CAN_BO NVARCHAR2(100) NOT NULL,
       TAI_KHOAN NVARCHAR2(10) UNIQUE NOT NULL,
       MAT_KHAU NVARCHAR2(100) NOT NULL,
       CMND NVARCHAR2(10),
       NGAY_CAP DATE,
       NOI_CAP NVARCHAR2(100),
       NOI_SINH NVARCHAR2(100),
       GIOI_TINH INT DEFAULT 0 NOT NULL ,
       MA_QUOC_TICH NVARCHAR2(100) NOT NULL REFERENCES HT_DM_QUOC_TICH(MA_QUOC_TICH),
       MA_DAN_TOC NVARCHAR2(100) NOT NULL REFERENCES HT_DM_DAN_TOC(MA_DAN_TOC),
       MA_TON_GIAO NVARCHAR2(100) NOT NULL REFERENCES HT_DM_TON_GIAO(MA_TON_GIAO),
       MA_HOC_HAM NVARCHAR2(100) NOT NULL REFERENCES HT_DM_HOC_HAM(MA_HOC_HAM),
       MA_HOC_VI NVARCHAR2(100) NOT NULL REFERENCES HT_DM_HOC_VI(MA_HOC_VI),
       DC_THUONG_TRU NVARCHAR2(100),
       DC_LIEN_LAC NVARCHAR2(100),
       DOAN_VIEN NVARCHAR2(100),
       NGAY_VAO_DANG DATE,
       NGAY_KY_HD DATE,
       NGAY_KET_THUC_HD DATE,
       DT_DI_DONG NVARCHAR2(20),
       DT_CO_DINH NVARCHAR2(20),
       EMAIL NVARCHAR2(50),
       HINH_ANH NVARCHAR2(100),
       SO_TAI_KHOAN NVARCHAR2(100),
       MA_SO_THUE NVARCHAR2(100),
       MA_QUYEN_LOAD_DAU_TIEN NVARCHAR2(100) REFERENCES PQ_DM_QUYEN(MA_QUYEN),
       STT_CAN_BO INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
/*
CREATE SEQUENCE SEQ_CAN_BO START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER TRI_CAN_BO BEFORE INSERT ON QLCB_DM_CAN_BO REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT SEQ_CAN_BO.NEXTVAL    INTO :NEW.MA_CAN_BO    FROM DUAL; END;
--1 2 3
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Hà Thanh Toàn','httoan' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Đỗ Văn Xê','dvxe' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI', 0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Trần Thị Thanh Hiền' ,'ttthien'    ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
--4 5 6
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Trần Cao Đệ','tcde' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Huỳnh Xuân Hiệp','hxhiep' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Ngô Bá Hùng','nbhung' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
--7 8 9 10
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Võ Huỳnh Trâm','vhtram' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Phan Phương Lan','pplan' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Trần Văn Hoàng','tvhoang' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Hồ Quang Thái','hqthai' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
--11 12 13
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Trần Nguyễn Minh Thư','tnmthu' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Phạm Nguyên Hoàng','pnhoang' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Võ Trí Thức','vtthuc' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
-- 14
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Nguyễn Thị Thủy Chung','nttchung' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
-- 15
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Lương Minh Liêm Pha','lmlpha' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
-- 16
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Đào Văn Phong','dvphong' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
--17
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Lương Văn Học','lvhoc' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
--18
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Nguyễn Thị Ngữ','ntngu' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);


--19 20
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Lương Văn Bộ','lvbo' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Nguyễn Thị Bộ','ntbo' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
--21 22
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Lương Văn Tạo','lvtao' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Nguyễn Thị Tạo','nttao' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
--23 24
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Lương Văn Toán','lvtoan' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);
INSERT INTO QLCB_DM_CAN_BO(TEN_CAN_BO,TAI_KHOAN, MAT_KHAU, GIOI_TINH, MA_QUOC_TICH, MA_DAN_TOC, MA_TON_GIAO, MA_HOC_HAM, MA_HOC_VI, NGUOI_CAP_NHAT, NGAY_CAP_NHAT, MA_QUYEN_LOAD_DAU_TIEN) 
                   VALUES('Nguyễn Thị Toán','nttoan' ,'123',0,'VIET_NAM', 'KINH', 'KHONG','GIANG_VIEN', 'THAC_SI',0, SYSDATE,NULL);














*/














---------------------------------------------PHÂN QUYỀN----------------------------------------




--DROP TABLE PQ_DM_CHUC_VU;
CREATE TABLE PQ_DM_CHUC_VU 
(
       MA_CHUC_VU NVARCHAR2(500) PRIMARY KEY,
       TEN_CHUC_VU NVARCHAR2(500) NOT NULL UNIQUE,
       STT_CHUC_VU INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('GIANG_VIEN','Giảng viên',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('HIEU_TRUONG','Hiệu trưởng',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('HIEU_PHO','Hiệu Phó',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('TRUONG_KHOA','Trưởng khoa',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('PHO_KHOA','Phó khoa',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('TRUONG_PHONG','Trưởng phòng',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('PHO_PHONG','Phó phòng',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('GIAM_DOC','Giám đốc',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('PHO_GIAM_DOC','Phó giám đốc',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('TRUONG_BO_MON','Trưởng bộ môn',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('PHO_BO_MON','Phó bộ môn',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('CHUYEN_VIEN','Chuyên viên',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('KE_TOAN','Kế toán',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('VAN_THU','Văn thư',0, SYSDATE,100);
INSERT INTO PQ_DM_CHUC_VU(MA_CHUC_VU,TEN_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_CHUC_VU) VALUES('GIAO_VU','Giáo vụ',0, SYSDATE,100);

--DROP TABLE PQ_DM_NHOM_QUYEN;
CREATE TABLE PQ_DM_NHOM_QUYEN 
(
       MA_NHOM_QUYEN NVARCHAR2(100) PRIMARY KEY,
       TEN_NHOM_QUYEN NVARCHAR2(100) NOT NULL UNIQUE,
       STT_NHOM_QUYEN INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);

INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('BAN_GIAM_HIEU','Ban giám hiệu',0, SYSDATE,1);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('LANH_DAO_KHOA_PHONG','Lãnh đạo khoa/phòng',0, SYSDATE,2);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('LANH_DAO_PHONG_TAI_CHINH_KE_TOAN','Lãnh đạo phòng tài chính kế toán',0, SYSDATE,3);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('LANH_DAO_PHONG_TO_CHUC_CAN_BO','Lãnh đạo phòng tổ chức cán bộ',0, SYSDATE,4);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('LANH_DAO_BO_MON','Lãnh đạo bộ môn',0, SYSDATE,5);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('CHUYEN_VIEN_PHONG_TAI_CHINH_KE_TOAN','Chuyên viên phòng tài chính kế toán',0, SYSDATE,6);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('CHUYEN_VIEN_PHONG_TO_CHUC_CAN_BO','Chuyên viên phòng tổ chức cán bộ',0, SYSDATE,7);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('CHUYEN_VIEN_PHONG','Chuyên viên phòng',0, SYSDATE,8);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('GIAO_VU_KHOA','Giáo vụ khoa',0, SYSDATE,9);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('GIAO_VU_BO_MON','Giáo vụ bộ môn',0, SYSDATE,10);
INSERT INTO PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN,TEN_NHOM_QUYEN,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NHOM_QUYEN) VALUES('GIANG_VIEN','Giảng viên',0, SYSDATE,11);

--DROP TABLE PQ_CO_QUYEN;
CREATE TABLE PQ_CO_QUYEN 
(
       MA_NHOM_QUYEN NVARCHAR2(100) NOT NULL REFERENCES PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN),
       MA_QUYEN NVARCHAR2(100) NOT NULL REFERENCES PQ_DM_QUYEN(MA_QUYEN),
       STT_CO_QUYEN INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       PRIMARY KEY (MA_NHOM_QUYEN,MA_QUYEN)
);
CREATE TABLE PQ_CO_NHOM_QUYEN 
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_NHOM_QUYEN NVARCHAR2(100) NOT NULL REFERENCES PQ_DM_NHOM_QUYEN(MA_NHOM_QUYEN),
       STT_CO_NHOM_QUYEN INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       PRIMARY KEY (MA_CAN_BO,MA_NHOM_QUYEN)
);
--DROP TABLE PQ_THUOC_TO_CHUYEN_MON;
CREATE TABLE PQ_THUOC_TO_CHUYEN_MON 
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_TO_CHUYEN_MON INT NOT NULL REFERENCES QLCB_DM_TO_CHUYEN_MON(MA_TO_CHUYEN_MON),
       MA_CHUC_VU NVARCHAR2(100) NOT NULL REFERENCES PQ_DM_CHUC_VU(MA_CHUC_VU),
       KIEM_NHIEM INT DEFAULT 0 NOT NULL,
       TRANG_THAI_THUOC_TO_CHUYEN_MON INT DEFAULT 0 NOT NULL, 
       TU_NGAY DATE NOT NULL,
       DEN_NGAY DATE,
       STT_THUOC_TO_CHUYEN_MON INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       PRIMARY KEY (MA_CAN_BO,MA_TO_CHUYEN_MON,TU_NGAY)
);
/*
-- Tổ chuyên môn














--1	Hà Thanh Toàn            1	Ban giám hiệu
--2	Đỗ Văn Xê                1	Ban giám hiệu
--3	Trần Thị Thanh Hiền      1	Ban giám hiệu
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(1,1,'HIEU_TRUONG',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(2,1,'HIEU_PHO',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(3,1,'HIEU_PHO',SYSDATE,0, SYSDATE,3);

--4	Trần Cao Đệ              2	Lãnh đạo khoa
--5	Huỳnh Xuân Hiệp          2	Lãnh đạo khoa
--6	Ngô Bá Hùng              2	Lãnh đạo khoa
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(4,2,'TRUONG_KHOA',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(5,2,'PHO_KHOA',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(6,2,'PHO_KHOA',SYSDATE,0, SYSDATE,3);
                           
--7	Võ Huỳnh Trâm            3	Bộ môn CNPM
--8	Phan Phương Lan          3	Bộ môn CNPM
--9	Trần Văn Hoàng           3	Bộ môn CNPM
--10	Hồ Quang Thái          3	Bộ môn CNPM
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(7,3,'TRUONG_BO_MON',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(8,3,'PHO_BO_MON',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(9,3,'GIANG_VIEN',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(10,3,'GIANG_VIEN',SYSDATE,0, SYSDATE,3);

--11	Trần Nguyễn Minh Thư   4	Bộ môn KHMT
--12	Phạm Nguyên Hoàng      4	Bộ môn KHMT
--13	Võ Trí Thức            4	Bộ môn KHMT
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(11,4,'TRUONG_BO_MON',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(12,4,'GIANG_VIEN',SYSDATE,0, SYSDATE,3);
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(13,4,'GIANG_VIEN',SYSDATE,0, SYSDATE,3);





--14	Nguyễn Thị Thủy Chung  7	Giáo vụ khoa
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(14,7,'GIAO_VU',SYSDATE,0, SYSDATE,3);
                           
--15	Lương Minh Liêm Pha    8	Lãnh đạo trung tâm
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(15,8,'GIAM_DOC',SYSDATE,0, SYSDATE,3);

--16	Đào Văn Phong          9	Tổ văn phòng
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(16,9,'CHUYEN_VIEN',SYSDATE,0, SYSDATE,3);
                           
--17	Lương Văn Học          10	Tổ tin học
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(17,10,'GIANG_VIEN',SYSDATE,0, SYSDATE,3);
                           
--18	Nguyễn Thị Ngữ         11	Tổ ngoại ngữ
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(18,11,'GIANG_VIEN',SYSDATE,0, SYSDATE,3);
                           
--19	Lương Văn Bộ           12	Lãnh đạo phòng
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(19,12,'TRUONG_PHONG',SYSDATE,0, SYSDATE,3);
                           
--20	Nguyễn Thị Bộ          13	Chuyên viên phòng
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(20,13,'CHUYEN_VIEN',SYSDATE,0, SYSDATE,3);
                           
--21	Lương Văn Tạo          14	Lãnh đạo phòng
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(21,14,'TRUONG_PHONG',SYSDATE,0, SYSDATE,3);
                           
--22	Nguyễn Thị Tạo         15	Chuyên viên phòng
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(22,15,'CHUYEN_VIEN',SYSDATE,0, SYSDATE,3);
                           
--23	Lương Văn Toán         16	Lãnh đạo phòng
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(23,16,'TRUONG_PHONG',SYSDATE,0, SYSDATE,3);
                           
--24	Nguyễn Thị Toán        17	Chuyên viên phòng
INSERT INTO PQ_THUOC_TO_CHUYEN_MON(MA_CAN_BO,MA_TO_CHUYEN_MON,MA_CHUC_VU,TU_NGAY,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_THUOC_TO_CHUYEN_MON) 
                           VALUES(24,17,'CHUYEN_VIEN',SYSDATE,0, SYSDATE,3);
*/








CREATE TABLE PQ_THAY_THE 
(
       MA_NGUOI_BAN INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_NGUOI_THAY_THE INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       NGAY_BAT_DAU DATE NOT NULL,
       NGAY_KET_THUC DATE,
       STT_THAY_THE INT DEFAULT 100 NOT NULL,
       LY_DO NVARCHAR2(500),
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       PRIMARY KEY (MA_NGUOI_BAN,MA_NGUOI_THAY_THE,NGAY_BAT_DAU)
);








-------------------------GIANG DAY
CREATE TABLE HT_DM_NAM_HOC
(
       TEN_NAM_HOC NVARCHAR2(100) PRIMARY KEY ,
       LA_NAM_HOC_HIEN_TAI INT DEFAULT 0 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
CREATE TABLE HT_DM_HOC_KY
(
       TEN_HOC_KY NVARCHAR2(100) PRIMARY KEY,
       TEN_NAM_HOC NVARCHAR2(100) NOT NULL REFERENCES HT_DM_NAM_HOC(TEN_NAM_HOC),
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);


--DROP TABLE CT_DM_LOAI_BCKH
CREATE TABLE CT_DM_LOAI_BCKH
(
       MA_LOAI_BCKH NVARCHAR2(200) PRIMARY KEY,
       TEN_LOAI_BCKH NVARCHAR2(200) NOT NULL,
       SO_GIO_CHUAN_QUI_DOI INT DEFAULT 0 NOT NULL,
       STT_LOAI_BCKH INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
--DROP TABLE CT_BCKH
CREATE TABLE CT_BCKH
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       TEN_HOC_KY NVARCHAR2(100) NOT NULL REFERENCES HT_DM_HOC_KY(TEN_HOC_KY),
       MA_LOAI_BCKH NVARCHAR2(100) NOT NULL REFERENCES CT_DM_LOAI_BCKH(MA_LOAI_BCKH),
       SO_GIO_CHUAN_QUI_DOI FLOAT NOT NULL,
       TEN_BCKH NVARCHAR2(100) NOT NULL,
       SO_TAP_CHI NVARCHAR2(100),
       VI_TRI_TRANG INT,
       TEN_NHA_XUAT_BAN NVARCHAR2(100),
       NOI_LUU_TRU NVARCHAR2(100),
       THOI_GIAN_DANG DATE,
       BO_MON_DUYET INT DEFAULT 0 NOT NULL,
       KHOA_DUYET INT DEFAULT 0 NOT NULL,
       TRUONG_DUYET INT DEFAULT 0 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       PRIMARY KEY (MA_CAN_BO,TEN_BCKH)
);
--DROP TABLE CT_DM_LOAI_NCKH
CREATE TABLE CT_DM_LOAI_NCKH
(
       MA_LOAI_NCKH NVARCHAR2(200) PRIMARY KEY,
       TEN_LOAI_NCKH NVARCHAR2(200) NOT NULL,
       SO_GIO_CHUAN_QUI_DOI INT DEFAULT 0 NOT NULL,
       STT_LOAI_NCKH INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
--DROP TABLE CT_NCKH
CREATE TABLE CT_NCKH
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       TEN_HOC_KY NVARCHAR2(100) NOT NULL REFERENCES HT_DM_HOC_KY(TEN_HOC_KY),
       MA_LOAI_NCKH NVARCHAR2(100) NOT NULL REFERENCES CT_DM_LOAI_NCKH(MA_LOAI_NCKH),
       SO_GIO_CHUAN_QUI_DOI FLOAT NOT NULL,
       TEN_NCKH NVARCHAR2(100) NOT NULL,
       CAP_BAC NVARCHAR2(100),
       SO_TIEN FLOAT,
       BO_MON_DUYET INT DEFAULT 0 NOT NULL,
       KHOA_DUYET INT DEFAULT 0 NOT NULL,
       TRUONG_DUYET INT DEFAULT 0 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       PRIMARY KEY (MA_CAN_BO,TEN_NCKH)
);
--DROP TABLE CT_DM_LOAI_GIAO_TRINH
CREATE TABLE CT_DM_LOAI_GIAO_TRINH
(
       MA_LOAI_GIAO_TRINH NVARCHAR2(100) PRIMARY KEY,
       TEN_LOAI_GIAO_TRINH NVARCHAR2(100) NOT NULL,
       SO_GIO_CHUAN_QUI_DOI INT DEFAULT 0 NOT NULL,
       STT_LOAI_GIAO_TRINH INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);


--DROP TABLE CT_GIAO_TRINH
CREATE TABLE CT_GIAO_TRINH
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       TEN_HOC_KY NVARCHAR2(100) NOT NULL REFERENCES HT_DM_HOC_KY(TEN_HOC_KY),
       MA_LOAI_GIAO_TRINH NVARCHAR2(100) NOT NULL REFERENCES CT_DM_LOAI_GIAO_TRINH(MA_LOAI_GIAO_TRINH),
       SO_GIO_CHUAN_QUI_DOI FLOAT NOT NULL,
       TEN_GIAO_TRINH NVARCHAR2(100) NOT NULL,
       TEN_NHA_XUAT_BAN NVARCHAR2(100),
       NOI_LUU_TRU NVARCHAR2(100),
       THOI_GIAN_DANG DATE,
       BO_MON_DUYET INT DEFAULT 0 NOT NULL,
       KHOA_DUYET INT DEFAULT 0 NOT NULL,
       TRUONG_DUYET INT DEFAULT 0 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       PRIMARY KEY (MA_CAN_BO,TEN_GIAO_TRINH)
);
--DROP TABLE CT_DM_LOAI_LOP_DAY
CREATE TABLE CT_DM_LOAI_LOP_DAY
(
       MA_LOAI_LOP_DAY NVARCHAR2(100) PRIMARY KEY,
       TEN_LOAI_LOP_DAY NVARCHAR2(100) NOT NULL,
       HE_SO_GIO_CHUAN FLOAT DEFAULT 0 NOT NULL,
       STT_LOAI_LOP_DAY INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
--DROP TABLE CT_LOP_GIANG_DAY
CREATE TABLE CT_LOP_GIANG_DAY
(
       MA_LOP_GIANG_DAY INT PRIMARY KEY,
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       TEN_HOC_KY NVARCHAR2(100) NOT NULL REFERENCES HT_DM_HOC_KY(TEN_HOC_KY),
       MA_LOAI_LOP_DAY NVARCHAR2(100) NOT NULL REFERENCES CT_DM_LOAI_LOP_DAY(MA_LOAI_LOP_DAY),
       HE_SO_GIO_CHUAN FLOAT NOT NULL,
       MA_NHOM_LOP_HOC NVARCHAR2(100) NOT NULL,
       TEN_MON_HOC NVARCHAR2(100) NOT NULL,
       SO_TIET FLOAT NOT NULL,
       SO_LUONG_SINH_VIEN INT NOT NULL,
       BO_MON_DUYET INT DEFAULT 0 NOT NULL,
       KHOA_DUYET INT DEFAULT 0 NOT NULL,
       TRUONG_DUYET INT DEFAULT 0 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
/*
DROP SEQUENCE SEQ_LOP_GIANG_DAY;
CREATE SEQUENCE SEQ_LOP_GIANG_DAY START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER TRI_LOP_GIANG_DAY BEFORE INSERT ON CT_LOP_GIANG_DAY REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT SEQ_LOP_GIANG_DAY.NEXTVAL INTO :NEW.MA_LOP_GIANG_DAY    FROM DUAL; END;
*/

CREATE TABLE CT_DM_LOAI_LOP_CO_VAN
(
       MA_LOAI_LOP_CO_VAN NVARCHAR2(100) PRIMARY KEY,
       TEN_LOAI_LOP_CO_VAN NVARCHAR2(100) NOT NULL,
       SO_GIO_CHUAN_QUI_DOI FLOAT DEFAULT 0 NOT NULL,
       STT_LOAI_LOP_CO_VAN INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
--DROP TABLE CT_LOP_CO_VAN
CREATE TABLE CT_LOP_CO_VAN
(
       MA_LOP_CO_VAN INT PRIMARY KEY,
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       TEN_HOC_KY NVARCHAR2(100) NOT NULL REFERENCES HT_DM_HOC_KY(TEN_HOC_KY),
       MA_LOAI_LOP_CO_VAN NVARCHAR2(100) NOT NULL REFERENCES CT_DM_LOAI_LOP_CO_VAN(MA_LOAI_LOP_CO_VAN),
       SO_GIO_CHUAN_QUI_DOI FLOAT NOT NULL,
       MA_LOP_HOC NVARCHAR2(100) NOT NULL,
       TEN_LOP_HOC NVARCHAR2(100) NOT NULL,
       SO_LUONG_SINH_VIEN INT NOT NULL,
       BO_MON_DUYET INT DEFAULT 0 NOT NULL,
       KHOA_DUYET INT DEFAULT 0 NOT NULL,
       TRUONG_DUYET INT DEFAULT 0 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
/*
DROP SEQUENCE SEQ_LOP_CO_VAN;
CREATE SEQUENCE SEQ_LOP_CO_VAN START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER TRI_LOP_CO_VAN BEFORE INSERT ON CT_LOP_CO_VAN REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT SEQ_LOP_CO_VAN.NEXTVAL INTO :NEW.MA_LOP_CO_VAN    FROM DUAL; END;
*/
CREATE TABLE CT_DM_LOAI_DAO_TAO
(
       MA_LOAI_DAO_TAO NVARCHAR2(100) PRIMARY KEY,
       TEN_LOAI_DAO_TAO NVARCHAR2(100) NOT NULL,
       PHAN_TRAM_GIAM_GIO INT DEFAULT 0 NOT NULL,
       STT_LOAI_DAO_TAO INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
--DROP TABLE CT_DUOC_DAO_TAO
CREATE TABLE CT_DUOC_DAO_TAO
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_LOAI_DAO_TAO NVARCHAR2(100) REFERENCES  CT_DM_LOAI_DAO_TAO(MA_LOAI_DAO_TAO),
       TEN_KHOA_HOC NVARCHAR2 (200) NOT NULL,
       THANG_BAT_DAU_HOC INT NOT NULL,
       NAM_BAT_DAU_HOC INT NOT NULL, 
       THANG_KET_THUC_HOC INT,
       NAM_KET_THUC_HOC INT,
       BO_MON_DUYET INT DEFAULT 0 NOT NULL,
       KHOA_DUYET INT DEFAULT 0 NOT NULL,
       TRUONG_DUYET INT DEFAULT 0 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       CONSTRAINT CT_QUA_TRINH_DAO_TAO_TBDH CHECK (THANG_BAT_DAU_HOC BETWEEN 1 and 12),
       CONSTRAINT CT_QUA_TRINH_DAO_TAO_NBDH CHECK (NAM_BAT_DAU_HOC BETWEEN 2013 and 2030),
       CONSTRAINT CT_QUA_TRINH_DAO_TAO_TKTH CHECK (THANG_KET_THUC_HOC BETWEEN 1 and 12),
       CONSTRAINT CT_QUA_TRINH_DAO_TAO_NKTH CHECK (NAM_KET_THUC_HOC BETWEEN 2013 and 2030),
       PRIMARY KEY (MA_CAN_BO,MA_LOAI_DAO_TAO,TEN_KHOA_HOC,THANG_BAT_DAU_HOC,NAM_BAT_DAU_HOC)
);


CREATE TABLE CT_DM_LOAI_THAI_SAN
(
       MA_LOAI_THAI_SAN NVARCHAR2(100) PRIMARY KEY,
       TEN_LOAI_THAI_SAN NVARCHAR2(100) NOT NULL,
       PHAN_TRAM_GIAM_GIO INT DEFAULT 0 NOT NULL,
       STT_LOAI_THAI_SAN INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
CREATE TABLE CT_HUONG_THAI_SAN
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_LOAI_THAI_SAN NVARCHAR2(100) REFERENCES  CT_DM_LOAI_THAI_SAN(MA_LOAI_THAI_SAN),
       SINH_CON_THU NVARCHAR2 (200) NOT NULL,
       THANG_BAT_DAU_HUONG INT NOT NULL,
       NAM_BAT_DAU_HUONG INT NOT NULL, 
       THANG_KET_THUC_HUONG INT,
       NAM_KET_THUC_HUONG INT,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       CONSTRAINT CT_HUONG_THAI_SAN_TBDH CHECK (THANG_BAT_DAU_HUONG BETWEEN 1 and 12),
       CONSTRAINT CT_HUONG_THAI_SAN_NBDH CHECK (NAM_BAT_DAU_HUONG BETWEEN 2013 and 2030),
       CONSTRAINT CT_HUONG_THAI_SAN_TKTH CHECK (THANG_KET_THUC_HUONG BETWEEN 1 and 12),
       CONSTRAINT CT_HUONG_THAI_SAN_NKTH CHECK (NAM_KET_THUC_HUONG BETWEEN 2013 and 2030),
       PRIMARY KEY (MA_CAN_BO,MA_LOAI_THAI_SAN,SINH_CON_THU,THANG_BAT_DAU_HUONG,NAM_BAT_DAU_HUONG)
);
---------------------------------------------QUẢN LÝ LƯƠNG----------------------------------------


CREATE TABLE HT_DM_TSHT
(
       LUONG_CO_BAN FLOAT DEFAULT 0 NOT NULL,
       PHAN_TRAM_DONG_BHXH FLOAT DEFAULT 0 NOT NULL,
       PHAN_TRAM_DONG_BHTN FLOAT DEFAULT 0 NOT NULL,
       GIO_CHUAN_TREN_NAM FLOAT DEFAULT 0 NOT NULL,
       THANG_BAT_DAU_AP_DUNG INT NOT NULL,
       NAM_BAT_DAU_AP_DUNG INT NOT NULL, 
       THANG_KET_THUC_AP_DUNG INT, -- thang ket thuc nay van con su dung tham so nay
       NAM_KET_THUC_AP_DUNG INT,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       CONSTRAINT QLL_DM_THAM_SO_HIEN_TAI_T CHECK (THANG_BAT_DAU_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_DM_THAM_SO_HIEN_TA_NI CHECK (NAM_BAT_DAU_AP_DUNG BETWEEN 2013 and 2030),
       PRIMARY KEY(THANG_BAT_DAU_AP_DUNG,NAM_BAT_DAU_AP_DUNG)
);

--DROP TABLE QLL_DM_NGACH;
CREATE TABLE QLL_DM_NGACH 
(
       MA_NGACH NVARCHAR2(500) PRIMARY KEY,
       TEN_NGACH NVARCHAR2(500) NOT NULL UNIQUE,
       STT_NGACH INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
INSERT INTO QLL_DM_NGACH(MA_NGACH,TEN_NGACH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NGACH) VALUES('THAC_SI','Thạc sĩ',0, SYSDATE,100);
INSERT INTO QLL_DM_NGACH(MA_NGACH,TEN_NGACH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NGACH) VALUES('KY_SU','Kỹ sư',0, SYSDATE,100);
INSERT INTO QLL_DM_NGACH(MA_NGACH,TEN_NGACH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NGACH) VALUES('CU_NHAN','Cử nhân',0, SYSDATE,100);
INSERT INTO QLL_DM_NGACH(MA_NGACH,TEN_NGACH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NGACH) VALUES('TIEN_SI','Tiến sĩ',0, SYSDATE,100);
INSERT INTO QLL_DM_NGACH(MA_NGACH,TEN_NGACH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NGACH) VALUES('CAO_DANG','Cao đẳng',0, SYSDATE,100);
INSERT INTO QLL_DM_NGACH(MA_NGACH,TEN_NGACH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NGACH) VALUES('TRUNG_CAP','Trung cấp',0, SYSDATE,100);
INSERT INTO QLL_DM_NGACH(MA_NGACH,TEN_NGACH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NGACH) VALUES('GIAO_SU','Giáo sư',0, SYSDATE,100);
INSERT INTO QLL_DM_NGACH(MA_NGACH,TEN_NGACH,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_NGACH) VALUES('PHO_GIAO_SU','Phó giáo sư',0, SYSDATE,100);



--DROP TABLE QLL_BAC_NGACH;
CREATE TABLE QLL_BAC_NGACH 
(
       MA_BAC_NGACH INT PRIMARY KEY,
       TEN_BAC_NGACH NVARCHAR2(100) NOT NULL UNIQUE ,
       MA_NGACH NVARCHAR2(100) NOT NULL REFERENCES QLL_DM_NGACH(MA_NGACH),
       HE_SO_LUONG FLOAT NOT NULL,
       STT_BAC_NGACH INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
/*

CREATE SEQUENCE SEQ_BAC_NGACH START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER TRI_BAC_NGACH BEFORE INSERT ON QLL_BAC_NGACH REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT SEQ_BAC_NGACH.NEXTVAL INTO :NEW.MA_BAC_NGACH    FROM DUAL; END;


INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 1 -  Kỹ sư','KY_SU',2.34,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 2 -  Kỹ sư','KY_SU',3,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 1 -  Cử nhân','CU_NHAN',2.34,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 2 -  Cử nhân','CU_NHAN',3,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 1 -  Thạc sĩ','THAC_SI',4,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 2 -  Thạc sĩ','THAC_SI',4.5,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 1 -  Tiến sĩ','TIEN_SI',5,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 2 -  Tiến sĩ','TIEN_SI',5.5,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 1 -  Phó giáo sư','PHO_GIAO_SU',6,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 2 -  Phó giáo sư','PHO_GIAO_SU',6.5,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 1 -  Giáo sư','GIAO_SU',7,0, SYSDATE,100);
INSERT INTO QLL_BAC_NGACH(TEN_BAC_NGACH,MA_NGACH,HE_SO_LUONG,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_BAC_NGACH) VALUES('Bậc 2 -  Giáo sư','GIAO_SU',7.5,0, SYSDATE,100);

*/
--DROP TABLE QLL_CO_LUONG
CREATE TABLE QLL_CO_LUONG
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_BAC_NGACH INT NOT NULL REFERENCES QLL_BAC_NGACH(MA_BAC_NGACH),
       HE_SO_LUONG FLOAT DEFAULT 0 NOT NULL,
       THANG_BAT_DAU_AP_DUNG INT NOT NULL,
       NAM_BAT_DAU_AP_DUNG INT NOT NULL, 
       THANG_KET_THUC_AP_DUNG INT,
       NAM_KET_THUC_AP_DUNG INT,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       CONSTRAINT QLL_QUA_TRINH_LUONG_TBDAD CHECK (THANG_BAT_DAU_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_QUA_TRINH_LUONG_NBDAD CHECK (NAM_BAT_DAU_AP_DUNG BETWEEN 2013 and 2030),
       CONSTRAINT QLL_QUA_TRINH_LUONG_TKTAD CHECK (THANG_KET_THUC_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_QUA_TRINH_LUONG_NKTAD CHECK (NAM_KET_THUC_AP_DUNG BETWEEN 2013 and 2030),
       PRIMARY KEY (MA_CAN_BO,THANG_BAT_DAU_AP_DUNG,NAM_BAT_DAU_AP_DUNG)
);
--DROP TABLE QLL_PHU_CAP_CHUC_VU 
CREATE TABLE QLL_PHU_CAP_CHUC_VU 
(
       MA_PHU_CAP_CHUC_VU INT PRIMARY KEY,
       TEN_PHU_CAP_CHUC_VU NVARCHAR2(100) NOT NULL UNIQUE ,
       MA_CHUC_VU NVARCHAR2(100) NOT NULL REFERENCES PQ_DM_CHUC_VU(MA_CHUC_VU),
       HE_SO_PHU_CAP_CHUC_VU FLOAT NOT NULL,
       STT_PHU_CAP_CHUC_VU INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
/*

CREATE SEQUENCE SEQ_PHU_CAP_CHUC_VU START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER TRI_PHU_CAP_CHUC_VU BEFORE INSERT ON QLL_PHU_CAP_CHUC_VU REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT SEQ_PHU_CAP_CHUC_VU.NEXTVAL INTO :NEW.MA_PHU_CAP_CHUC_VU    FROM DUAL; END;


INSERT INTO QLL_PHU_CAP_CHUC_VU(TEN_PHU_CAP_CHUC_VU,MA_CHUC_VU,HE_SO_PHU_CAP_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_CHUC_VU) VALUES('Phụ cấp hiệu trưởng','HIEU_TRUONG',3,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_CHUC_VU(TEN_PHU_CAP_CHUC_VU,MA_CHUC_VU,HE_SO_PHU_CAP_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_CHUC_VU) VALUES('Phụ cấp hiệu phó','HIEU_PHO',2.5,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_CHUC_VU(TEN_PHU_CAP_CHUC_VU,MA_CHUC_VU,HE_SO_PHU_CAP_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_CHUC_VU) VALUES('Phụ cấp trưởng khoa','TRUONG_KHOA',2,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_CHUC_VU(TEN_PHU_CAP_CHUC_VU,MA_CHUC_VU,HE_SO_PHU_CAP_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_CHUC_VU) VALUES('Phụ cấp phó khoa','PHO_KHOA',3,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_CHUC_VU(TEN_PHU_CAP_CHUC_VU,MA_CHUC_VU,HE_SO_PHU_CAP_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_CHUC_VU) VALUES('Phụ cấp trưởng bộ môn','TRUONG_BO_MON',1.5,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_CHUC_VU(TEN_PHU_CAP_CHUC_VU,MA_CHUC_VU,HE_SO_PHU_CAP_CHUC_VU,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_CHUC_VU) VALUES('Phụ cấp phó bộ môn','PHO_BO_MON',1,0, SYSDATE,100);

*/

--DROP TABLE QLL_CO_PHU_CAP_CHUC_VU
CREATE TABLE QLL_CO_PHU_CAP_CHUC_VU
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_PHU_CAP_CHUC_VU INT NOT NULL REFERENCES QLL_PHU_CAP_CHUC_VU(MA_PHU_CAP_CHUC_VU),
       HE_SO_PHU_CAP_CHUC_VU FLOAT DEFAULT 0 NOT NULL,
       THANG_BAT_DAU_AP_DUNG INT NOT NULL,
       NAM_BAT_DAU_AP_DUNG INT NOT NULL, 
       THANG_KET_THUC_AP_DUNG INT,
       NAM_KET_THUC_AP_DUNG INT,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       CONSTRAINT QLL_CO_PHU_CAP_CHUC_VU_TBDAD CHECK (THANG_BAT_DAU_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_CO_PHU_CAP_CHUC_VU_NBDAD CHECK (NAM_BAT_DAU_AP_DUNG BETWEEN 2013 and 2030),
       CONSTRAINT QLL_CO_PHU_CAP_CHUC_VU_TKTAD CHECK (THANG_KET_THUC_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_CO_PHU_CAP_CHUC_VU_NKTAD CHECK (NAM_KET_THUC_AP_DUNG BETWEEN 2013 and 2030),
       PRIMARY KEY (MA_CAN_BO,THANG_BAT_DAU_AP_DUNG,NAM_BAT_DAU_AP_DUNG)
);


--DROP TABLE QLL_PHU_CAP_KHAC 
CREATE TABLE QLL_PHU_CAP_KHAC
(
       MA_PHU_CAP_KHAC INT PRIMARY KEY,
       TEN_PHU_CAP_KHAC NVARCHAR2(100) NOT NULL UNIQUE ,
       SO_TIEN_PHU_CAP_KHAC FLOAT NOT NULL,
       STT_PHU_CAP_KHAC INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
/*

CREATE SEQUENCE SEQ_PHU_CAP_KHAC START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER TRI_PHU_CAP_KHAC BEFORE INSERT ON QLL_PHU_CAP_KHAC REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT SEQ_PHU_CAP_KHAC.NEXTVAL INTO :NEW.MA_PHU_CAP_KHAC    FROM DUAL; END;


INSERT INTO QLL_PHU_CAP_KHAC(TEN_PHU_CAP_KHAC,HE_SO_PHU_CAP_KHAC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_KHAC) VALUES('Phụ cấp điện thoại gói 100k',100000,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_KHAC(TEN_PHU_CAP_KHAC,HE_SO_PHU_CAP_KHAC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_KHAC) VALUES('Phụ cấp điện thoại gói 200k',200000,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_KHAC(TEN_PHU_CAP_KHAC,HE_SO_PHU_CAP_KHAC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_KHAC) VALUES('Phụ cấp điện thoại gói 300k',300000,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_KHAC(TEN_PHU_CAP_KHAC,HE_SO_PHU_CAP_KHAC,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_KHAC) VALUES('Phụ cấp phương tiện đi lại ',300000,0, SYSDATE,100);

*/
--DROP TABLE QLL_CO_PHU_CAP_KHAC
CREATE TABLE QLL_CO_PHU_CAP_KHAC
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_PHU_CAP_KHAC INT NOT NULL REFERENCES QLL_PHU_CAP_KHAC(MA_PHU_CAP_KHAC),
       SO_TIEN_PHU_CAP_KHAC FLOAT NOT NULL,
       THANG_BAT_DAU_AP_DUNG INT NOT NULL,
       NAM_BAT_DAU_AP_DUNG INT NOT NULL, 
       THANG_KET_THUC_AP_DUNG INT,
       NAM_KET_THUC_AP_DUNG INT,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       CONSTRAINT QLL_CO_PHU_CAP_KHAC_TBDAD CHECK (THANG_BAT_DAU_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_CO_PHU_CAP_KHAC_NBDAD CHECK (NAM_BAT_DAU_AP_DUNG BETWEEN 2013 and 2030),
       CONSTRAINT QLL_CO_PHU_CAP_KHAC_TKTAD CHECK (THANG_KET_THUC_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_CO_PHU_CAP_KHAC_NKTAD CHECK (NAM_KET_THUC_AP_DUNG BETWEEN 2013 and 2030),
       PRIMARY KEY (MA_CAN_BO,THANG_BAT_DAU_AP_DUNG,NAM_BAT_DAU_AP_DUNG)
);
--DROP TABLE QLL_PHU_CAP_UU_DAI 
CREATE TABLE QLL_PHU_CAP_UU_DAI
(
       MA_PHU_CAP_UU_DAI INT PRIMARY KEY,
       TEN_PHU_CAP_UU_DAI NVARCHAR2(100) NOT NULL UNIQUE ,
       HE_SO_PHU_CAP_UU_DAI FLOAT NOT NULL,
       STT_PHU_CAP_UU_DAI INT DEFAULT 100 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE
);
/*

CREATE SEQUENCE SEQ_PHU_CAP_UU_DAI START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER TRI_PHU_CAP_UU_DAI BEFORE INSERT ON QLL_PHU_CAP_UU_DAI REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT SEQ_PHU_CAP_UU_DAI.NEXTVAL INTO :NEW.MA_PHU_CAP_UU_DAI    FROM DUAL; END;


INSERT INTO QLL_PHU_CAP_UU_DAI(TEN_PHU_CAP_UU_DAI,HE_SO_PHU_CAP_UU_DAI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_UU_DAI) VALUES('Phụ cấp điện thoại gói 100k',100000,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_UU_DAI(TEN_PHU_CAP_UU_DAI,HE_SO_PHU_CAP_UU_DAI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_UU_DAI) VALUES('Phụ cấp điện thoại gói 200k',200000,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_UU_DAI(TEN_PHU_CAP_UU_DAI,HE_SO_PHU_CAP_UU_DAI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_UU_DAI) VALUES('Phụ cấp điện thoại gói 300k',300000,0, SYSDATE,100);
INSERT INTO QLL_PHU_CAP_UU_DAI(TEN_PHU_CAP_UU_DAI,HE_SO_PHU_CAP_UU_DAI,NGUOI_CAP_NHAT,NGAY_CAP_NHAT,STT_PHU_CAP_UU_DAI) VALUES('Phụ cấp phương tiện đi lại ',300000,0, SYSDATE,100);

*/
--DROP TABLE QLL_CO_PHU_CAP_UU_DAI
CREATE TABLE QLL_CO_PHU_CAP_UU_DAI
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       MA_PHU_CAP_UU_DAI INT NOT NULL REFERENCES QLL_PHU_CAP_UU_DAI(MA_PHU_CAP_UU_DAI),
       HE_SO_PHU_CAP_UU_DAI FLOAT NOT NULL,
       THANG_BAT_DAU_AP_DUNG INT NOT NULL,
       NAM_BAT_DAU_AP_DUNG INT NOT NULL, 
       THANG_KET_THUC_AP_DUNG INT,
       NAM_KET_THUC_AP_DUNG INT,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       CONSTRAINT QLL_CO_PHU_CAP_UU_DAI_TBDAD CHECK (THANG_BAT_DAU_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_CO_PHU_CAP_UU_DAI_NBDAD CHECK (NAM_BAT_DAU_AP_DUNG BETWEEN 2013 and 2030),
       CONSTRAINT QLL_CO_PHU_CAP_UU_DAI_TKTAD CHECK (THANG_KET_THUC_AP_DUNG BETWEEN 1 and 12),
       CONSTRAINT QLL_CO_PHU_CAP_UU_DAI_NKTAD CHECK (NAM_KET_THUC_AP_DUNG BETWEEN 2013 and 2030),
       PRIMARY KEY (MA_CAN_BO,THANG_BAT_DAU_AP_DUNG,NAM_BAT_DAU_AP_DUNG)
);
-- DROP TABLE QLL_BANG_LUONG
CREATE TABLE QLL_BANG_LUONG
(
       MA_CAN_BO INT NOT NULL REFERENCES QLCB_DM_CAN_BO(MA_CAN_BO),
       THANG_LANH_LUONG INT NOT NULL,
       NAM_LANH_LUONG INT NOT NULL,
       TIEN_LUONG_CO_BAN FLOAT DEFAULT 0 NOT NULL,
       TIEN_PHU_CAP_CHUC_VU FLOAT DEFAULT 0  NOT NULL,
       TONG_TIEN_PHU_CAP_KHAC FLOAT DEFAULT 0  NOT NULL,
       TIEN_PHU_CAP_THAM_NIEN FLOAT DEFAULT 0  NOT NULL,
       TIEN_LUONG_THEM_GIO FLOAT DEFAULT 0 NOT NULL,
       TIEN_THUONG FLOAT DEFAULT 0 NOT NULL,
       TIEN_DONG_BAO_HIEM_XA_HOI FLOAT DEFAULT 0  NOT NULL,
       TIEN_DONG_BAO_HIEM_TAI_NAN FLOAT DEFAULT 0 NOT NULL,
       TIEN_DONG_THUE_THU_NHAP FLOAT DEFAULT 0 NOT NULL,
       NGAY_LANH_LAN_1 DATE,
       SO_TIEN_LANH_LAN_1 FLOAT DEFAULT 0 NOT NULL,
       NGAY_LANH_LAN_2 DATE,
       SO_TIEN_LANH_LAN_2 FLOAT DEFAULT 0 NOT NULL,
       TRANG_THAI INT DEFAULT 0 NOT NULL,
       NGUOI_CAP_NHAT INT,
       NGAY_CAP_NHAT DATE,
       NGUOI_XOA INT,
       NGAY_XOA DATE,
       CONSTRAINT QLL_BANG_LUONG_T CHECK (THANG_LANH_LUONG BETWEEN 1 and 12),
       CONSTRAINT QLL_BANG_LUONG_N CHECK (NAM_LANH_LUONG BETWEEN 2013 and 2030),
       PRIMARY KEY (MA_CAN_BO,THANG_LANH_LUONG,NAM_LANH_LUONG)
);
